package org.jdbc.dao.impl;

import org.common.dao.IEmployeeDAO;
import org.common.dao.impl.BaseDAOImpl;
import org.common.entity.Employee;
import org.common.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

/**
 * @author yexi
 */
public class EmployeeDAOImpl extends BaseDAOImpl implements IEmployeeDAO {

    @Override
    public int insert(Employee employee) {
        String sql = "insert into employee values(DEFAULT,?,?,?,?,?,?,?)";
        return baseUpdate(sql, employee.getEmpName(), employee.getJob(), employee.getMgr(),
                employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo());
    }

    @Override
    public int deleteById(Long id) {
        int rows = 0;
        try (Connection connection = JDBCUtil.getConnection()) {
            String sql = "delete from employee where emp_no =?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                preparedStatement.setObject(1, id);
                rows = preparedStatement.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public int updateById(Employee employee) {
        String sql = "update employee set emp_name= ?,job=?,mgr=?,hiredate=?,salary=?,bonus=?,dept_no=? where emp_no = ?";
        return baseUpdate(sql, employee.getEmpName(), employee.getJob(), employee.getMgr(),
                employee.getHiredate(), employee.getSalary(), employee.getBonus(), employee.getDeptNo(), employee.getEmpNo());
    }

    @Override
    public Employee selectById(Long id) {
        String sql = "select * from employee where emp_no = ?";
        return baseQuery(Employee.class, sql, id);
    }

    @Override
    public List<Employee> selectAll() {
        String sql = "select * from employee";
        return baseQueryList(Employee.class, sql);
    }

    @Override
    public int selectCount() {
        String sql = "select count(*) from employee";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                // 获取第一列的值
                count = resultSet.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(preparedStatement, connection);
        }
        return count;
    }

}
